package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import Modelo.Persona;
import DBConection.ConexionBD;

public class PersonaDao {

    private Connection conn;

    public PersonaDao() {
        conn = ConexionBD.getConnection();
    }

    public void addPersona(Persona Objpersona) {
       
    	
    	try {
        	  
        	String sql = "INSERT INTO "+ConexionBD.getNombre_BD()+".TBL_Personas(Nombres,Apellidos,Sexo,Direccion,Correos,identificacion,tipo_identificacion)" +
                " VALUES (?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            
            ps.setString(1, Objpersona.getNombre());
            ps.setString(2, Objpersona.getApellido());
            ps.setString(3, Objpersona.getSexo());
            ps.setString(4, Objpersona.getDireccion());
            ps.setString(5, Objpersona.getCorreos());
            ps.setInt(6, Objpersona.getIdentificacion());
            ps.setString(7, Objpersona.getTipo_Indentificacion());
            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void removePersona(int personaId) {
        try {
                String sql = "DELETE FROM "+ConexionBD.getNombre_BD()+".TBL_Personas WHERE personaId=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, personaId);
            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
      }

    public void editUser(Persona Objpersona) {           
        try {
                String sql = "UPDATE "+ConexionBD.getNombre_BD()+".TBL_Personas SET Nombres=?,Apellidos=?,Sexo=?,Direccion=?,Correos=?" +
            " WHERE personaId=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            
            ps.setString(1, Objpersona.getNombre());
            ps.setString(2, Objpersona.getApellido());
            ps.setString(3, Objpersona.getSexo());
            ps.setString(4, Objpersona.getDireccion());
            ps.setString(5, Objpersona.getCorreos());
            ps.setInt(6, Objpersona.getId());
            ps.executeUpdate();            

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public List<Persona> getAllPersonas() {
        List<Persona> users = new ArrayList();
        try {
            String sql = "SELECT * FROM "+ConexionBD.getNombre_BD()+".TBL_Personas";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Persona userBean = new Persona();
                userBean.setId(rs.getInt("PERSONAID"));
                userBean.setNombre(rs.getString("NOMBRES"));
                userBean.setApellido(rs.getString("APELLIDOS"));   
                userBean.setSexo(rs.getString("SEXO")); 
                userBean.setDireccion(rs.getString("DIRECCION"));
                userBean.setCorreos(rs.getString("CORREOS"));
                users.add(userBean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return users;
    }

    public Persona getUserById(int userId) {
    	Persona userBean = new Persona();
        try {
                String sql = "SELECT * FROM "+ConexionBD.getNombre_BD()+".TBL_Personas WHERE personaId=?";
            PreparedStatement ps = conn.
                    prepareStatement(sql);
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
            	userBean.setId(rs.getInt("PERSONAID"));
                userBean.setNombre(rs.getString("NOMBRES"));
                userBean.setApellido(rs.getString("APELLIDOS"));   
                userBean.setSexo(rs.getString("SEXO")); 
                userBean.setDireccion(rs.getString("DIRECCION"));
                userBean.setCorreos(rs.getString("CORREOS"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userBean;
    }
}